import requests
from lxml import etree
import DBUtil
import Sha1Util
from retrying import retry

def getCityUrl():
    """
    获取城市的url和id
    :return:
    """
    sql = 'select id,url,status from city' #查询城市表，获取每一个城市的id和url
    result = DBUtil.queryData(sql)

    return result

def getAreaData():
    """
    获取区域数据
    :return:
    """
    creadeTable() #创建表结构
    result = getCityUrl() #获取城市url
    if result != 500:
        for row in result:
            city_id = row[0]
            url = row[1]
            status = row[2]
            try:
                response = getAreaFromWeb(url)
                if response.status_code == 200:
                    page_text = response.text
                    tree = etree.HTML(page_text)
                    area_list = tree.xpath(
                        '//div[@class="cnts"]/ul/li[3]/a/text()')
                    area_url_list = tree.xpath('//ul[@class="box"]//li[3]/a/@href')
                    if len(area_list) > 0:
                        area_list.pop(0)  #去出不限字段
                        area_url_list.pop(0)
                    area_dict = dict(zip(area_list, area_url_list))
                    # 将获取的数据写入数据库
                    # for areaName, areaUrl in area_dict.items():
                    for areaName, areaurl in area_dict.items():
                        id = Sha1Util.jia_mi(city_id + areaName)
                        areaUrl = url[0:-1] + areaurl
                        sql2 = 'insert into area1 (id,name,cityId,areaUrl) values ("%s","%s","%s","%s")' % (id,areaName,city_id,areaUrl)
                        result = DBUtil.modifyData(sql2)
                        if result == 200 : #修改成功
                            #修改city表的status
                            sql = 'update city set status = 1 where cityId = "%s"' % city_id
                            DBUtil.modifyData(sql)
                        elif result == 500 :
                            sql = 'update city set status = -1 where cityId = "%s"' % city_id
                            DBUtil.modifyData(sql)
                else :
                    sql = 'update city set status = -1 where cityId = "%s"' % city_id
                    DBUtil.modifyData(sql)
                    pass
            except Exception as e :
                print("获取区域信息失败 %s " % e)

@retry(stop_max_attempt_number=3,wait_random_min=1000,wait_random_max=5000)
def getAreaFromWeb(url):
    """
    获取网络 区域数据
    :param url:
    :return:
    """
    headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36",
        'Connection': 'close'
    }
    response = requests.get(url=url,headers=headers)
    response.encoding = "utf-8"
    return response

def creadeTable():
    sql = """
        CREATE TABLE if not exists `area1` (
        `id` varchar(50) NOT NULL,
        `name` varchar(20) DEFAULT NULL COMMENT '区县',
        `cityId` varchar(50) DEFAULT NULL COMMENT '所属城市id',
        `areaUrl` varchar(50) DEFAULT NULL COMMENT '区域url',
        `status` int(1) DEFAULT 0 COMMENT '状态，获取到岗位信息为1，未获取到为-1', 
         PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
    """
    DBUtil.createDatabase(sql)


creadeTable()
getCityUrl()
getAreaData()